package com.kaifamiao.jdbc.features;

import java.sql.*;

/**
 * 1、获得 Statement 并指定结果集的
 *      connection.createStatement( int type, int concurrency )
 *      connection.createStatement( int type, int concurrency, int holdability)
 *
 * 2、获得 PreparedStatement 并指定结果集的 滚动特性、并发特性、可保存性
 *      connection.prepareStatement(String sql, int type, int concurrency)
 *      connection.prepareStatement(String sql, int type, int concurrency, int holdability)
 */
public class ResultSetScrollable {

    public static void show( ResultSet rs ) throws SQLException {
        int deptno = rs.getInt( "deptno" );
        double min = rs.getDouble( "minsal" );
        double avg = rs.getDouble( "avgsal" );
        double max = rs.getDouble( "maxsal" );
        System.out.printf("%d, %d, %d, %d \n", deptno, Math.round(min), Math.round(avg), Math.round(max) );
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        /*
        String driverClassName = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/kaifamiao?serverTimezone=Asia/Chongqing";
        String username = "kfm";
        String password = "kfm";
        */

        String driverClassName = "oracle.jdbc.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:kaifamiao";
        String username = "kaifamiao";
        String password = "kaifamiao";

        Class.forName( driverClassName );
        Connection conn = DriverManager.getConnection( url, username, password );
        // 关闭事务自动提交
        conn.setAutoCommit( false );

        String sql = """
                        SELECT deptno, min(sal) minsal, avg(sal) avgsal, max(sal) maxsal
                        FROM emp WHERE deptno IS NOT NULL GROUP BY deptno ORDER BY deptno
                     """;
        final int type = ResultSet.TYPE_SCROLL_INSENSITIVE; // 可滚动
        final int concurrent = ResultSet.CONCUR_UPDATABLE; // 可更新
        final int holdability = ResultSet.HOLD_CURSORS_OVER_COMMIT; // 可保存(事务提交后不关闭结果集)
        // PreparedStatement ps = conn.prepareStatement( sql, type, concurrent );
        PreparedStatement ps = conn.prepareStatement( sql, type, concurrent, holdability );

        ResultSet rs = ps.executeQuery();
        // 判断是否存在下一行
        while( rs.next() ){
            show( rs );
        }

        System.out.println( rs.next() ); // false

        conn.commit(); //即使事务已经提交但结果集仍然处于开启状态

        // 判断是否存在上一行
        while( rs.previous() ){
           show( rs );
        }

        // 采用绝对定位，定位到第1行
        boolean exists = rs.absolute(1);
        System.out.println( exists );
        if( exists ) { // 若成功定位到第 1 行
            show( rs ); // 则显示第 1 行各个列的值
        }

        // 采用相对定位，向下移动1行
        exists = rs.relative(1);
        System.out.println( exists );
        if( exists ) { // 若成功定位到下 1 行
            show( rs ); // 则显示该行各个列的值
        }

        // 采用相对定位，向上移动1行
        exists = rs.relative(-1);
        System.out.println( exists );
        if( exists ) { // 若成功定位到上 1 行
            show( rs ); // 则显示该行各个列的值
        }

        // 启用事务自动提交
        conn.setAutoCommit( true );

        rs.close();
        ps.close();

        // conn.createStatement( type, concurrent );
        Statement stmt = conn.createStatement( type, concurrent, holdability );
        rs = stmt.executeQuery( sql );

        exists = rs.last();
        System.out.println( exists );
        if( exists ) {
            // 先处理最后一行
            show(rs);
            // 然后再依次处理之前的各行
            while (rs.relative(-1)) {
                show(rs);
            }
        }

        rs.close();
        stmt.close();

        conn.close();

    }

}
